package com.example.booksystem.dao;

import com.example.booksystem.bean.Type;
import com.example.booksystem.bean.User;
import com.example.booksystem.utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class TypeDao {
    //添加图书
    public int add(String name,long parentId){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int result = 0;

        try {
            connection = DBUtil.getConnection();
            String sql = "insert into type values(null,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,name);
            preparedStatement.setLong(2,parentId);
            result = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,preparedStatement,resultSet);
        }
        return result;
    }
    //修改图书
    public int modify(long id,String name,long parentId){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int result = 0;

        try {
            connection = DBUtil.getConnection();
            String sql = "update type set name=?,parentId=? where id=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,name);
            preparedStatement.setLong(2,parentId);
            preparedStatement.setLong(3,id);
            result = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,preparedStatement,resultSet);
        }
        return result;
    }
    //删除图书
    public int remove(long id){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int result = 0;

        try {
            connection = DBUtil.getConnection();
            String sql = "delete from type where id=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setLong(1,id);
            result = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,preparedStatement,resultSet);
        }
        return result;
    }
    //查询所有图书类型
    public List<Type> getAll(){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ArrayList<Type> list = new ArrayList<>();

        try {
            connection = DBUtil.getConnection();
            String sql = "select id,name,parentId from type";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                Type type = new Type();
                type.setId(resultSet.getLong(1));
                type.setName(resultSet.getString(2));
                type.setParentId(resultSet.getLong(3));
                list.add(type);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,preparedStatement,resultSet);
        }
        return list;
    }
    //查询图书类型(按照id)
    public Type getById(long id){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Type type = new Type();

        try {
            connection = DBUtil.getConnection();
            String sql = "select id,name,parentId from type where id=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setLong(1,id);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                type.setId(resultSet.getLong(1));
                type.setName(resultSet.getString(2));
                type.setParentId(resultSet.getLong(3));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,preparedStatement,resultSet);
        }
        return type;
    }
}
